package com.zl.dao;

import java.sql.Connection;
import java.sql.ResultSet;

import javax.naming.spi.DirStateFactory.Result;

import com.mysql.jdbc.PreparedStatement;
import com.zl.model.Book;
import com.zl.util.ZLString;

public class BookDao {

	//增删改查
	
	/**
	 * 新增图书
	 * @param connection
	 * @param book
	 * @return
	 * @throws Exception
	 */
	public static int add(Connection connection, Book book) throws Exception {
		
		String sql = "insert into t_book (bookName, author, sex, price, bookTypeId, bookDesc) values (?, ?, ?, ?, ?, ?)";
		
		PreparedStatement statement = (PreparedStatement) connection.prepareStatement(sql);
		statement.setString(1, book.getBookName());
		statement.setString(2, book.getAuthor());
		statement.setString(3, book.getSex());
		statement.setFloat(4, book.getPrice());
		statement.setInt(5, book.getBookTypeId());
		statement.setString(6, book.getBookDesc());
		
		return statement.executeUpdate();
	}
	
	/**
	 * 删除图书
	 * @param connection
	 * @param id
	 * @return
	 * @throws Exception
	 */
	public static int delete(Connection connection, String id) throws Exception {
		
		String sql = "delete from t_book where id = ?";
		
		PreparedStatement statement = (PreparedStatement) connection.prepareStatement(sql);
		statement.setString(1, id);
		
		return statement.executeUpdate();
	}
	
	/**
	 * 修改图书
	 * @param connection
	 * @param book
	 * @return
	 * @throws Exception
	 */
	public static int update(Connection connection, Book book) throws Exception {
		
		String sql = "update t_book set bookName = ?, author = ?, sex = ?, price = ?, bookTypeId = ?, bookDesc = ? where id = ?";
		
		PreparedStatement statement = (PreparedStatement) connection.prepareStatement(sql);
		statement.setString(1, book.getBookName());
		statement.setString(2, book.getAuthor());
		statement.setString(3, book.getSex());
		statement.setFloat(4, book.getPrice());
		statement.setInt(5, book.getBookTypeId());
		statement.setString(6, book.getBookDesc());
		statement.setInt(7, book.getId());
		
		return statement.executeUpdate();
	}
	
	public static ResultSet select(Connection connection, Book book) throws Exception {

		//根据图书类别id取对应图书的结果集
		StringBuffer buffer = new StringBuffer("select * from t_book b,t_bookType bt where b.bookTypeId = bt.id");
		if(ZLString.isNotEmpty(book.getBookName())){
			buffer.append(" and b.bookName like '%" + book.getBookName() + "%'");
		}
		if(ZLString.isNotEmpty(book.getAuthor())){
			buffer.append(" and b.author like '%" + book.getAuthor() + "%'");
		}
		if(book.getBookTypeId()!=null && book.getBookTypeId()!=-1){
			buffer.append(" and b.bookTypeId=" + book.getBookTypeId());
		}
		PreparedStatement statement = (PreparedStatement) connection.prepareStatement(buffer.toString());
		
		return statement.executeQuery();
	}
	
	/**
	 * bookTypeId对应的图书类别下存不存在图书
	 * @param connection
	 * @param bookTypeId
	 * @return
	 * @throws Exception
	 */
	public boolean hasBookByBookTypeId(Connection connection, String bookTypeId) throws Exception {
		
		String sql = "select * from t_book where bookTypeId = ?";
		
		PreparedStatement statement = (PreparedStatement) connection.prepareStatement(sql);
		statement.setString(1, bookTypeId);
		
		ResultSet set = statement.executeQuery();
		return set.next();
	}
	
}
